using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Xml.Schema;
using System.IO;
using System.Data;
using RedGate.SQLCompare.Engine;
using RedGate.SQL.Shared;
using RedGate.SQLDataCompare.Engine.ResultsStore;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLPackager.Engine;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Reflection;
using System.Diagnostics;
using Stratman.Utilities;

namespace Stratman.DesktopProcess.DatabaseSynchronizer
{
    /// <summary>
    /// Serves as a base class for the various synchronization modes and provides a number of
    /// functions and properties common to all modes.
    /// </summary>
    public class SynchronizerProject : IDisposable
    {
        /// <summary>
        /// Common options in the synchronization process.
        /// </summary>
        protected SynchronizationOptions synchronizationOptions = new SynchronizationOptions();
        /// <summary>
        /// Contains the various TraceListener objects responsible for writing output to the 
        /// console or to a log file.
        /// </summary>
        protected TraceSource traceSource = new TraceSource("SynchronizerProject", SourceLevels.All);
        /// <summary>
        /// Flag indicating whether we should actually make changes to the target database or
        /// filesystem or just report on what would have happened.
        /// </summary>
        protected bool noChanges = false;

        /// <summary>
        /// Collection of TraceListener objects registered to receive events from the application.
        /// </summary>
        public TraceListenerCollection TraceListeners
        {
            get
            {
                return traceSource.Listeners;
            }
        }
        
        /// <summary>
        /// Serves as the "factory" method for loading synchronizer projects:  it gets the type
        /// of project we are loading from the project file and then instantiates a copy of the
        /// appropriate object before calling LoadFromFile() on that object instance.
        /// </summary>
        /// <param name="projectFilePath">
        /// File from which we are to load our project settings.
        /// </param>
        /// <param name="commandLineArguments">
        /// Command line arguments that were specified.
        /// </param>
        /// <returns>
        /// An instantiated copy of the appropriate synchronizer object.
        /// </returns>
        public static SynchronizerProject Load(string projectFilePath, string[] commandLineArguments)
        {
            // Load the project file
            XmlDocument projectFile = new XmlDocument();
            projectFile.Load(projectFilePath);

            // Get and instantiate the appropriate child class
            string projectType = projectFile.SelectSingleNode("/SynchronizerProject/@Type").InnerText;
            SynchronizerProject projectInstance = (SynchronizerProject)Assembly.GetExecutingAssembly().CreateInstance(projectType);

            // Instantiate the member variables from the command line arguments
            foreach (string argument in commandLineArguments)
            {
                if (argument.ToLower() == "/nochanges")
                    projectInstance.noChanges = true;
            }

            // Perform class-specific loading logic
            projectInstance.LoadFromFile(projectFilePath, commandLineArguments);

            return projectInstance;
        }

        /// <summary>
        /// Saves the project's settings to disk.
        /// </summary>
        /// <param name="projectFilePath">
        /// File into which we are to save our settings.
        /// </param>
        public virtual void Save(string projectFilePath)
        {
            throw new NotImplementedException("Classes that inherit from SynchronizerProject must define their own version of the Save() method.");
        }

        /// <summary>
        /// Writes a trace message with an event type of "Verbose".
        /// </summary>
        /// <param name="message">
        /// Message that we wish to write.
        /// </param>
        protected void TraceVerbose(string message)
        {
            traceSource.TraceEvent(TraceEventType.Verbose, 0, message);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Verbose".
        /// </summary>
        /// <param name="format">
        /// Format string to use when creating the final message.
        /// </param>
        /// <param name="args">
        /// Values that will be inserted into the format string.
        /// </param>
        protected void TraceVerbose(string format, params object[] args)
        {
            traceSource.TraceEvent(TraceEventType.Verbose, 0, format, args);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Information".
        /// </summary>
        /// <param name="message">
        /// Message that we wish to write.
        /// </param>
        protected void TraceInformation(string message)
        {
            traceSource.TraceEvent(TraceEventType.Information, 0, message);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Information".
        /// </summary>
        /// <param name="format">
        /// Format string to use when creating the final message.
        /// </param>
        /// <param name="args">
        /// Values that will be inserted into the format string.
        /// </param>
        protected void TraceInformation(string format, params object[] args)
        {
            traceSource.TraceEvent(TraceEventType.Information, 0, format, args);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Warning".
        /// </summary>
        /// <param name="message">
        /// Message that we wish to write.
        /// </param>
        protected void TraceWarning(string message)
        {
            traceSource.TraceEvent(TraceEventType.Warning, 0, message);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Warning".
        /// </summary>
        /// <param name="format">
        /// Format string to use when creating the final message.
        /// </param>
        /// <param name="args">
        /// Values that will be inserted into the format string.
        /// </param>
        protected void TraceWarning(string format, params object[] args)
        {
            traceSource.TraceEvent(TraceEventType.Warning, 0, format, args);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Error".
        /// </summary>
        /// <param name="message">
        /// Message that we wish to write.
        /// </param>
        protected void TraceError(string message)
        {
            traceSource.TraceEvent(TraceEventType.Error, 0, message);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Error".
        /// </summary>
        /// <param name="format">
        /// Format string to use when creating the final message.
        /// </param>
        /// <param name="args">
        /// Values that will be inserted into the format string.
        /// </param>
        protected void TraceError(string format, params object[] args)
        {
            traceSource.TraceEvent(TraceEventType.Error, 0, format, args);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Critical".
        /// </summary>
        /// <param name="message">
        /// Message that we wish to write.
        /// </param>
        protected void TraceCritical(string message)
        {
            traceSource.TraceEvent(TraceEventType.Critical, 0, message);
        }

        /// <summary>
        /// Writes a trace message with an event type of "Critical".
        /// </summary>
        /// <param name="format">
        /// Format string to use when creating the final message.
        /// </param>
        /// <param name="args">
        /// Values that will be inserted into the format string.
        /// </param>
        protected void TraceCritical(string format, params object[] args)
        {
            traceSource.TraceEvent(TraceEventType.Critical, 0, format, args);
        }

        /// <summary>
        /// Load the project's settings from disk.
        /// </summary>
        /// <param name="projectFilePath">
        /// File from which we are to load our settings.
        /// </param>
        /// <param name="commandLineArguments">
        /// Command line arguments that were specified.
        /// </param>
        protected virtual void LoadFromFile(string projectFilePath, string[] commandLineArguments)
        {
            throw new NotImplementedException("Classes that inherit from SynchronizerProject must define their own version of the LoadFromFile() method.");
        }

        /// <summary>
        /// Perform the necessary synchronization operations depending on the type of project.
        /// </summary>
        public virtual void Execute()
        {
            throw new NotImplementedException("Classes that inherit from SynchronizerProject must define their own version of the Execute() method.");
        }

        /// <summary>
        /// Takes two pre-existing databases and upgrades the schema and data of the target 
        /// database to make it equivalent to the source database.
        /// </summary>
        /// <param name="synchronizationOptions">
        /// Options for the synchronization operation.
        /// </param>
        protected void SynchronizeTwoDatabases(SynchronizationOptions synchronizationOptions)
        {
            Database sourceDatabaseInstance = new Database();
            Database targetDatabaseInstance = new Database();
            ExecutionBlock executionBlock = null;
            ComparisonSession dataComparisonSession = null;

            // Set the status callback handler for the Red Gate objects so that we can write status
            // updates to the screen and to the log
            sourceDatabaseInstance.Status = new StatusEventHandler(StatusUpdate);
            targetDatabaseInstance.Status = new StatusEventHandler(StatusUpdate);

            try
            {
                // Register the source and target databases for schema comparison
                TraceInformation("Registering source database ({0}.{1}) for schema comparison", synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase);
                sourceDatabaseInstance.Register(CreateConnectionProperties(synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase, synchronizationOptions.SourceUserName, synchronizationOptions.SourcePassword), Options.Default | Options.ForceColumnOrder);

                TraceInformation("Registering target database ({0}.{1}) for schema comparison", synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase);
                targetDatabaseInstance.Register(CreateConnectionProperties(synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword), Options.Default | Options.ForceColumnOrder);

                TraceInformation("Comparing the two databases");

                // Get the differences between the two databases
                Differences differences = sourceDatabaseInstance.CompareWith(targetDatabaseInstance, Options.Default | Options.ForceColumnOrder);

                TraceInformation("Evaluating differences");

                foreach (Difference difference in differences)
                {
                    // Only include differences that the options indicate we should process
                    if (!ShouldProcessDifference(difference, synchronizationOptions))
                    {
                        difference.Selected = false;
                        continue;
                    }

                    if (difference.Type == DifferenceType.Different)
                        TraceVerbose("The {0} {1} is different in the source database and will be updated in the target", StringUtilities.DePascalCase(difference.ObjectIn1.ObjectType.ToString()).ToLower(), GetFileName(difference.ObjectIn1.FullyQualifiedName, false));

                    else if (difference.Type == DifferenceType.OnlyIn1)
                        TraceVerbose("The {0} {1} exists only in the source database and will be created in the target", StringUtilities.DePascalCase(difference.ObjectIn1.ObjectType.ToString()).ToLower(), GetFileName(difference.ObjectIn1.FullyQualifiedName, false));

                    else if (difference.Type == DifferenceType.OnlyIn2)
                        TraceVerbose("The {0} {1} does not exist in the source database and will be dropped from the target", StringUtilities.DePascalCase(difference.ObjectIn2.ObjectType.ToString()).ToLower(), GetFileName(difference.ObjectIn2.FullyQualifiedName, false));
                }

                BlockExecutor executor = null;

                // Build the necessary synchronization scripts to make the schema of the target 
                // database equivalent to that of the source
                if (!noChanges)
                {
                    Work work = new Work();
                    work.BuildFromDifferences(differences, Options.Default | Options.ForceColumnOrder, true);

                    executionBlock = work.ExecutionBlock;
                    executor = new BlockExecutor();

                    TraceInformation("Updating target database");

                    // Run the schema synchronization scripts
                    if (String.IsNullOrEmpty(synchronizationOptions.TargetUserName) && String.IsNullOrEmpty(synchronizationOptions.TargetPassword))
                        executor.ExecuteBlock(work.ExecutionBlock, synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase);

                    else
                        executor.ExecuteBlock(work.ExecutionBlock, synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, false, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword);

                    executionBlock.Dispose();
                }

                // Process any static tables specified in the options
                if (synchronizationOptions.StaticTables.Count > 0)
                {
                    // Register the source and target databases for data comparison
                    TraceInformation("Registering source database ({0}.{1}) for data comparison", synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase);
                    sourceDatabaseInstance.RegisterForDataCompare(CreateConnectionProperties(synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase, synchronizationOptions.SourceUserName, synchronizationOptions.SourcePassword));

                    TraceInformation("Registering target database ({0}.{1}) for data comparison", synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase);
                    targetDatabaseInstance.RegisterForDataCompare(CreateConnectionProperties(synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase, synchronizationOptions.TargetUserName, synchronizationOptions.TargetPassword));

                    SchemaMappings mappings = new SchemaMappings();
                    mappings.CreateMappings(sourceDatabaseInstance, targetDatabaseInstance);

                    // Only synchronize data for static tables specified in the options
                    foreach (TableMapping mapping in mappings.TableMappings)
                    {
                        if (!synchronizationOptions.StaticTables.Contains(mapping.Obj1.FullyQualifiedName))
                            mappings.TableMappings[mapping.Obj1.FullyQualifiedName].Include = false;
                    }

                    TraceInformation("Comparing the two databases");

                    // Get the differences in data between the two databases
                    dataComparisonSession = new ComparisonSession();
                    dataComparisonSession.CompareDatabases(sourceDatabaseInstance, targetDatabaseInstance, mappings);

                    TraceInformation("Evaluating differences");

                    // Output a summary of the differences
                    foreach (TableMapping mapping in mappings.TableMappings)
                    {
                        int rowsOnlyInSource = 0;
                        int rowsOnlyInTarget = 0;
                        int rowsDifferent = 0;

                        TableDifference difference = dataComparisonSession.TableDifferences[mapping.Obj1.FullyQualifiedName];

                        if (difference != null)
                        {
                            foreach (Row row in difference.ResultsStore)
                            {
                                if (row.Type == Row.RowType.Different)
                                    rowsDifferent++;

                                else if (row.Type == Row.RowType.In1)
                                    rowsOnlyInSource++;

                                else if (row.Type == Row.RowType.In2)
                                    rowsOnlyInTarget++;
                            }
                        }

                        if (rowsDifferent > 0)
                            TraceVerbose("There are {0} rows in {1} that are different in the source database and will be updated in the target", rowsDifferent, GetFileName(mapping.Obj1.FullyQualifiedName, false));

                        if (rowsOnlyInSource > 0)
                            TraceVerbose("There are {0} rows in {1} that exist only in the source database and will be created in the target", rowsOnlyInSource, GetFileName(mapping.Obj1.FullyQualifiedName, false));

                        if (rowsOnlyInTarget > 0)
                            TraceVerbose("There are {0} rows in {1} that do not exist in the source database and will be dropped from the target", rowsOnlyInTarget, GetFileName(mapping.Obj1.FullyQualifiedName, false));
                    }

                    // Get and run the necessary data synchronization scripts
                    if (!noChanges)
                    {
                        TraceInformation("Updating target database");

                        SqlProvider provider = new SqlProvider();
                        executionBlock = provider.GetMigrationSQL(dataComparisonSession, true);

                        executor.ExecuteBlock(executionBlock, synchronizationOptions.TargetServerName, synchronizationOptions.TargetDatabase);
                    }
                }
            }

            catch (SqlException exception)
            {
                TraceCritical("Database error occurred during synchronization: {0}", (exception.InnerException != null ? exception.InnerException.Message : exception.Message));
                throw;
            }

            catch (Exception exception)
            {
                TraceCritical("Error occurred during synchronization: {0}", exception.Message);
                throw;
            }

            finally
            {
                // Cleanup the various objects
                sourceDatabaseInstance.Dispose();
                targetDatabaseInstance.Dispose();

                if (executionBlock != null)
                    executionBlock.Dispose();

                if (dataComparisonSession != null)
                    dataComparisonSession.Dispose();
            }
        }

        /// <summary>
        /// Creates a ConnectionProperties object for the given server and authentication 
        /// information.
        /// </summary>
        /// <param name="serverName">
        /// Name of the server that we are connecting to.
        /// </param>
        /// <param name="database">
        /// Name of the database that we are connecting to.
        /// </param>
        /// <param name="userName">
        /// Username to use during authentication; leave this and the password blank to use Windows
        /// authentication.
        /// </param>
        /// <param name="password">
        /// Password to use during authentication; leave this and the username blank to use Windows
        /// authentication.
        /// </param>
        /// <returns>
        /// A ConnectionProperties instance for the given information.
        /// </returns>
        protected ConnectionProperties CreateConnectionProperties(string serverName, string database, string userName, string password)
        {
            // Use Windows authentication if the username and password are emtpy
            if (String.IsNullOrEmpty(userName) && String.IsNullOrEmpty(password))
                return new ConnectionProperties(serverName, database);

            return new ConnectionProperties(serverName, database, userName, password);
        }

        /// <summary>
        /// Creates a SqlConnection object for the given server and authentication information.
        /// </summary>
        /// <param name="serverName">
        /// Name of the server that we are connecting to.
        /// </param>
        /// <param name="database">
        /// Name of the database that we are connecting to.
        /// </param>
        /// <param name="userName">
        /// Username to use during authentication; leave this and the password blank to use Windows
        /// authentication.
        /// </param>
        /// <param name="password">
        /// Password to use during authentication; leave this and the username blank to use Windows
        /// authentication.
        /// </param>
        /// <returns>
        /// A SqlConnection instance for the given information.
        /// </returns>
        protected SqlConnection CreateConnection(string serverName, string database, string userName, string password)
        {
            // Use Windows authentication if the username and password are emtpy
            string connectionString = "Data Source=" + serverName + ";Initial Catalog=" + database + ";";
            connectionString += (String.IsNullOrEmpty(userName) && String.IsNullOrEmpty(password) ? "Integrated Security=SSPI" : "User ID=" + userName + ";Password=" + password);

            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            return connection;
        }

        /// <summary>
        /// Runs the T-SQL contained in a given file using a given connection.
        /// </summary>
        /// <param name="filePath">
        /// File whose T-SQL we are to run.
        /// </param>
        /// <param name="connection">
        /// Connection against which we should run the script.
        /// </param>
        protected void RunSqlScript(string filePath, ServerConnection connection)
        {
            if (noChanges)
                return;

            StreamReader fileReader = new StreamReader(new FileStream(filePath, FileMode.Open));
            string commandText = fileReader.ReadToEnd();

            try
            {
                connection.ExecuteNonQuery(commandText);
            }

            finally
            {
                fileReader.Close();
            }
        }

        /// <summary>
        /// Status callback for the Red Gate objects so that we can print status updates to the
        /// screen and to the log.
        /// </summary>
        /// <param name="sender">
        /// Object from which this event originated.
        /// </param>
        /// <param name="e">
        /// Arguments associated with the event.
        /// </param>
        protected void StatusUpdate(object sender, StatusEventArgs e)
        {
            if (!String.IsNullOrEmpty(e.Message))
                TraceVerbose(e.Message);
        }

        /// <summary>
        /// Gets the filename to use for a fully-qualified object name:  transforms
        /// [dbo].[TestTable1] into dbo.TestTable1.sql.
        /// </summary>
        /// <param name="databaseObjectName">
        /// Object name that we are to transform.
        /// </param>
        /// <returns>
        /// The filename for the given database object.
        /// </returns>
        protected string GetFileName(string databaseObjectName)
        {
            return GetFileName(databaseObjectName, true);
        }

        /// <summary>
        /// Gets the filename to use for a fully-qualified object name:  transforms
        /// [dbo].[TestTable1] into dbo.TestTable1.sql.
        /// </summary>
        /// <param name="databaseObjectName">
        /// Object name that we are to transform.
        /// </param>
        /// <param name="includeExtension">
        /// Flag indicating whether or not to include the ".sql" extension.
        /// </param>
        /// <returns>
        /// The filename for the given database object.
        /// </returns>
        protected string GetFileName(string databaseObjectName, bool includeExtension)
        {
            return databaseObjectName.Replace("[", "").Replace("]", "") + (includeExtension ? ".sql" : "");
        }

        /// <summary>
        /// Gets the fully qualified name of a database object from its associated filename:
        /// transforms dbo.TestTable1.sql into [dbo].[TestTable1].
        /// </summary>
        /// <param name="fileName">
        /// The filename that we are to transform.
        /// </param>
        /// <returns>
        /// The fully-qualified name of the given database object.
        /// </returns>
        protected string GetFullyQualifiedName(string fileName)
        {
            if (fileName.EndsWith(".sql"))
                fileName = fileName.Substring(0, fileName.Length - 4);

            return "[" + fileName.Replace(".", "].[") + "]";
        }

        /// <summary>
        /// Checks to see if we should process a given database difference, according to the
        /// synchronization options.  We should process it if the options indicate that we are
        /// to process all objects of the given database type or if the specific object is included
        /// on the list of objects to process for the given type.
        /// </summary>
        /// <param name="difference">
        /// Difference that we are to check.
        /// </param>
        /// <param name="synchronizationOptions">
        /// Synchronization options that we are to check against.
        /// </param>
        /// <returns>
        /// True if we should process the difference, false otherwise.
        /// </returns>
        protected bool ShouldProcessDifference(Difference difference, SynchronizationOptions synchronizationOptions)
        {
            if (difference.Type == DifferenceType.Equal)
                return false;

            return ShouldProcessDifference(difference.DatabaseObjectType, difference.Name, synchronizationOptions);
        }

        /// <summary>
        /// Checks to see if we should process a given database difference, according to the
        /// synchronization options.  We should process it if the options indicate that we are
        /// to process all objects of the given database type or if the specific object is included
        /// on the list of objects to process for the given type.
        /// </summary>
        /// <param name="objectName">
        /// Name of the object that we are to check.
        /// </param>
        /// <param name="objectType">
        /// Type of the object that we are to check.
        /// </param>
        /// <param name="synchronizationOptions">
        /// Synchronization options that we are to check against.
        /// </param>
        /// <returns>
        /// True if we should process the difference, false otherwise.
        /// </returns>
        protected bool ShouldProcessDifference(ObjectType objectType, string objectName, SynchronizationOptions synchronizationOptions)
        {
            return (synchronizationOptions.ProcessAllOfObjectType[objectType] || synchronizationOptions.IncludedObjects[objectType].Contains(objectName));
        }

        /// <summary>
        /// Explicitly disposes the object; flushes and closes the various trace sources.
        /// </summary>
        public void Dispose()
        {
            traceSource.Flush();
            traceSource.Close();
        }
    }
}
